use sqlx::sqlite::SqlitePool;
use sqlx::{Error, Row, FromRow};
use serde::{Deserialize, Serialize};
use chrono::{DateTime, Utc, NaiveDateTime};

#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
pub struct Device {
    pub id: String,
    pub name: String,
    pub device_type: String,
    pub platform: String,
    pub status: String,
    pub connection_config: Option<String>,
    pub device_info: Option<String>,
    pub performance_config: Option<String>,
    pub proxy_config: Option<String>,
    pub last_heartbeat: Option<NaiveDateTime>,
    pub last_error: Option<String>,
    pub uptime_seconds: Option<i64>,
    pub total_tasks_completed: i64,
    pub total_errors: i64,
    pub created_at: NaiveDateTime,
    pub updated_at: NaiveDateTime,
}

#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
pub struct SocialAccount {
    pub id: String,
    pub platform: String,
    pub username: String,
    pub display_name: Option<String>,
    pub email: Option<String>,
    pub encrypted_credentials: Vec<u8>,
    pub credential_type: String,
    pub account_info: Option<String>,
    pub security_settings: Option<String>,
    pub operation_settings: Option<String>,
    pub assigned_device_id: Option<String>,
    pub status: String,
    pub last_login: Option<NaiveDateTime>,
    pub last_activity: Option<NaiveDateTime>,
    pub login_failures: i64,
    pub created_at: NaiveDateTime,
    pub updated_at: NaiveDateTime,
}

#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
pub struct Task {
    pub id: String,
    pub name: String,
    pub task_type: String,
    pub category: String,
    pub strategy_id: Option<String>,
    pub account_id: String,
    pub device_id: Option<String>,
    pub parent_task_id: Option<String>,
    pub status: String,
    pub priority: i64,
    pub scheduled_at: Option<NaiveDateTime>,
    pub deadline: Option<NaiveDateTime>,
    pub config: String,
    pub input_data: Option<String>,
    pub output_data: Option<String>,
    pub started_at: Option<NaiveDateTime>,
    pub completed_at: Option<NaiveDateTime>,
    pub execution_duration: Option<i64>,
    pub retry_count: i64,
    pub max_retries: i64,
    pub retry_delay: i64,
    pub error_message: Option<String>,
    pub error_code: Option<String>,
    pub error_details: Option<String>,
    pub progress_percentage: i64,
    pub current_step: Option<String>,
    pub total_steps: i64,
    pub created_at: NaiveDateTime,
    pub updated_at: NaiveDateTime,
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct ContentItem {
    pub id: String,
    pub title: Option<String>,
    pub text: Option<String>,
    pub platform: String,
    pub account_id: String,
    pub status: String,
    pub media_urls: Option<String>, // JSON 数组
    pub tags: Option<String>, // JSON 数组
    pub scheduled_at: Option<i64>,
    pub published_at: Option<i64>,
    pub content_data: Option<String>, // JSON 字段存储额外数据
    pub created_at: i64,
    pub updated_at: i64,
}

#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
pub struct TaskLog {
    pub id: i64,
    pub task_id: String,
    pub log_level: String,
    pub message: String,
    pub details: Option<String>,
    pub step_name: Option<String>,
    pub step_index: Option<i64>,
    pub screenshot_path: Option<String>,
    pub attachments: Option<String>,
    pub timestamp: NaiveDateTime,
}

#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
pub struct SystemConfig {
    pub id: i64,
    pub config_key: String,
    pub config_value: String,
    pub description: Option<String>,
    pub config_type: String,
    pub is_encrypted: bool,
    pub created_at: NaiveDateTime,
    pub updated_at: NaiveDateTime,
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct DatabaseStats {
    pub devices_count: u64,
    pub accounts_count: u64,
    pub tasks_count: u64,
    pub content_count: u64,
}

pub struct Database {
    pool: SqlitePool,
}

impl Database {
    pub fn new(pool: SqlitePool) -> Self {
        Self { pool }
    }

    pub async fn migrate(&self) -> Result<(), Error> {
        // 创建设备表
        sqlx::query(
            r#"
            CREATE TABLE IF NOT EXISTS devices (
                id TEXT PRIMARY KEY,
                name TEXT NOT NULL,
                device_id TEXT UNIQUE NOT NULL,
                device_type TEXT NOT NULL CHECK (device_type IN ('android', 'ios', 'simulator', 'cloud')),
                platform TEXT NOT NULL,
                model TEXT,
                resolution TEXT,
                version TEXT,
                status TEXT NOT NULL DEFAULT 'offline' CHECK (status IN ('online', 'offline', 'busy', 'connecting')),
                last_active INTEGER,
                created_at INTEGER NOT NULL,
                updated_at INTEGER NOT NULL
            )
            "#,
        )
        .execute(&self.pool)
        .await?;

        // 创建社交账号表
        sqlx::query(
            r#"
            CREATE TABLE IF NOT EXISTS social_accounts (
                id TEXT PRIMARY KEY,
                username TEXT NOT NULL,
                platform TEXT NOT NULL CHECK (platform IN ('tiktok', 'instagram', 'facebook', 'twitter', 'youtube', 'linkedin')),
                status TEXT NOT NULL DEFAULT 'inactive' CHECK (status IN ('active', 'inactive', 'suspended', 'pending')),
                followers INTEGER,
                following INTEGER,
                posts INTEGER,
                last_login INTEGER,
                account_data TEXT, -- JSON
                created_at INTEGER NOT NULL,
                updated_at INTEGER NOT NULL,
                UNIQUE(username, platform)
            )
            "#,
        )
        .execute(&self.pool)
        .await?;

        // 创建任务表
        sqlx::query(
            r#"
            CREATE TABLE IF NOT EXISTS tasks (
                id TEXT PRIMARY KEY,
                name TEXT NOT NULL,
                task_type TEXT NOT NULL CHECK (task_type IN ('post', 'like', 'follow', 'comment', 'share', 'custom')),
                status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'running', 'paused', 'completed', 'failed')),
                account_id TEXT,
                device_id TEXT,
                description TEXT,
                config TEXT, -- JSON
                progress_completed INTEGER DEFAULT 0,
                progress_total INTEGER DEFAULT 0,
                scheduled_at INTEGER,
                started_at INTEGER,
                completed_at INTEGER,
                created_at INTEGER NOT NULL,
                updated_at INTEGER NOT NULL,
                FOREIGN KEY (account_id) REFERENCES social_accounts (id) ON DELETE SET NULL,
                FOREIGN KEY (device_id) REFERENCES devices (id) ON DELETE SET NULL
            )
            "#,
        )
        .execute(&self.pool)
        .await?;

        // 创建内容表
        sqlx::query(
            r#"
            CREATE TABLE IF NOT EXISTS content_items (
                id TEXT PRIMARY KEY,
                title TEXT,
                text TEXT,
                platform TEXT NOT NULL,
                account_id TEXT NOT NULL,
                status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'scheduled', 'published', 'failed')),
                media_urls TEXT, -- JSON array
                tags TEXT, -- JSON array
                scheduled_at INTEGER,
                published_at INTEGER,
                content_data TEXT, -- JSON
                created_at INTEGER NOT NULL,
                updated_at INTEGER NOT NULL,
                FOREIGN KEY (account_id) REFERENCES social_accounts (id) ON DELETE CASCADE
            )
            "#,
        )
        .execute(&self.pool)
        .await?;

        // 创建任务日志表
        sqlx::query(
            r#"
            CREATE TABLE IF NOT EXISTS task_logs (
                id TEXT PRIMARY KEY,
                task_id TEXT NOT NULL,
                level TEXT NOT NULL CHECK (level IN ('debug', 'info', 'warn', 'error')),
                message TEXT NOT NULL,
                details TEXT, -- JSON
                timestamp INTEGER NOT NULL,
                FOREIGN KEY (task_id) REFERENCES tasks (id) ON DELETE CASCADE
            )
            "#,
        )
        .execute(&self.pool)
        .await?;

        // 创建系统配置表
        sqlx::query(
            r#"
            CREATE TABLE IF NOT EXISTS system_config (
                id TEXT PRIMARY KEY,
                key TEXT UNIQUE NOT NULL,
                value TEXT NOT NULL,
                category TEXT,
                description TEXT,
                created_at INTEGER NOT NULL,
                updated_at INTEGER NOT NULL
            )
            "#,
        )
        .execute(&self.pool)
        .await?;

        // 创建索引
        self.create_indexes().await?;

        // 插入默认配置
        self.insert_default_config().await?;

        Ok(())
    }

    async fn create_indexes(&self) -> Result<(), Error> {
        let indexes = vec![
            "CREATE INDEX IF NOT EXISTS idx_devices_status ON devices (status)",
            "CREATE INDEX IF NOT EXISTS idx_devices_type ON devices (device_type)",
            "CREATE INDEX IF NOT EXISTS idx_accounts_platform ON social_accounts (platform)",
            "CREATE INDEX IF NOT EXISTS idx_accounts_status ON social_accounts (status)",
            "CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks (status)",
            "CREATE INDEX IF NOT EXISTS idx_tasks_type ON tasks (task_type)",
            "CREATE INDEX IF NOT EXISTS idx_tasks_account ON tasks (account_id)",
            "CREATE INDEX IF NOT EXISTS idx_tasks_device ON tasks (device_id)",
            "CREATE INDEX IF NOT EXISTS idx_tasks_scheduled ON tasks (scheduled_at)",
            "CREATE INDEX IF NOT EXISTS idx_content_platform ON content_items (platform)",
            "CREATE INDEX IF NOT EXISTS idx_content_account ON content_items (account_id)",
            "CREATE INDEX IF NOT EXISTS idx_content_status ON content_items (status)",
            "CREATE INDEX IF NOT EXISTS idx_logs_task ON task_logs (task_id)",
            "CREATE INDEX IF NOT EXISTS idx_logs_level ON task_logs (level)",
            "CREATE INDEX IF NOT EXISTS idx_logs_timestamp ON task_logs (timestamp)",
            "CREATE INDEX IF NOT EXISTS idx_config_category ON system_config (category)",
        ];

        for index in indexes {
            sqlx::query(index).execute(&self.pool).await?;
        }

        Ok(())
    }

    async fn insert_default_config(&self) -> Result<(), Error> {
        let now = chrono::Utc::now().timestamp();
        let default_configs = vec![
            ("app_version", "1.0.0", "app", "应用版本号"),
            ("max_concurrent_tasks", "5", "tasks", "最大并发任务数"),
            ("task_timeout", "300", "tasks", "任务超时时间(秒)"),
            ("device_check_interval", "30", "devices", "设备检查间隔(秒)"),
            ("auto_retry_failed_tasks", "true", "tasks", "自动重试失败任务"),
            ("log_level", "info", "logging", "日志级别"),
            ("log_retention_days", "30", "logging", "日志保留天数"),
        ];

        for (key, value, category, description) in default_configs {
            sqlx::query(
                r#"
                INSERT OR IGNORE INTO system_config (id, key, value, category, description, created_at, updated_at)
                VALUES (?, ?, ?, ?, ?, ?, ?)
                "#,
            )
            .bind(uuid::Uuid::new_v4().to_string())
            .bind(key)
            .bind(value)
            .bind(category)
            .bind(description)
            .bind(now)
            .bind(now)
            .execute(&self.pool)
            .await?;
        }

        Ok(())
    }

    pub async fn health_check(&self) -> Result<bool, Error> {
        let row = sqlx::query("SELECT 1 as health").fetch_one(&self.pool).await?;
        Ok(row.get::<i32, _>("health") == 1)
    }

    pub fn pool(&self) -> &SqlitePool {
        &self.pool
    }
}

// 数据库错误类型
#[derive(Debug, thiserror::Error)]
pub enum DatabaseError {
    #[error("SQLx database error: {0}")]
    Sqlx(#[from] sqlx::Error),
    
    #[error("Serialization error: {0}")]
    Serialization(#[from] serde_json::Error),
    
    #[error("Not found")]
    NotFound,
    
    #[error("Constraint violation: {0}")]
    ConstraintViolation(String),
    
    #[error("Invalid data: {0}")]
    InvalidData(String),
}